import baostock as bs
import datetime
import pandas as pd
import pymysql

import time
from dateutil import parser

db = pymysql.Connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='',
    db='gupiao',
    charset='utf8'
)
cursor = db.cursor()


def getClosebydate2(code1, date, year):
    try:
        startdate = datetime.datetime(year=date.year, month=date.month, day=date.day)
        if year == 5:
            startdate = startdate - datetime.timedelta(days=1830)
        else:
            startdate = startdate - datetime.timedelta(days=3655)

        enddate = startdate + datetime.timedelta(days=10)

        rs1 = bs.query_history_k_data_plus(code1,
                                           "date,code,close",
                                           start_date=startdate.strftime('%Y-%m-%d'),
                                           end_date=enddate.strftime('%Y-%m-%d'),
                                           frequency="m", adjustflag="2")
        h = 0
        while (rs1.error_code == '0') & rs1.next():
            one1 = rs1.get_row_data()
            h = float(one1[2])
            break
    except Exception as e:
        print(e)

    return h


def getClosebydate(code1, date, year):
    h = 0
    try:
        startdate = datetime.datetime(year=date.year, month=date.month, day=date.day)
        if year == 5:
            startdate = startdate - datetime.timedelta(days=1830)
        else:
            startdate = startdate - datetime.timedelta(days=3655)

        enddate = startdate + datetime.timedelta(days=31)

        sql = "SELECT close FROM `fa_stock_mk` where date > '" + startdate.strftime(
            '%Y-%m-%d') + "' and date < '" + enddate.strftime('%Y-%m-%d') + "' and code = '" + code1 + "' limit 1"
        result = cursor.execute(sql)

        if result:
            dataone = cursor.fetchone()
            h = float(dataone[0])
    except Exception as e:
        print(e)

    return h


def updateK2DB(datalist):
    global cursor
    global db

    for j in range(0, len(datalist)):
        data = datalist[j]

        b5 = b10 = 0

        h5 = getClosebydate(data[2], data[1], 5)
        if h5 > 0:
            b5 = float(data[3]) / h5
        else:
            continue

        h10 = getClosebydate(data[2], data[1], 10)
        if h10 > 0:
            b10 = float(data[3]) / h10

        try:
            sql = "UPDATE fa_stock_mk SET B5='" + str(b5) + "', B10='" + str(b10) \
                  + "' WHERE id=" + str(data[0])
            cursor.execute(sql)
            db.commit()
        except Exception as e:
            print(e)
            print(sql)
            return False

    return True


# val[0:-1]表示第一个元素到倒数第二个元素的切片
def queryKdata():
    # 5563268
    i = 0
    while i <= 5563268:
        try:
            sql = "SELECT id, date, code, close FROM `fa_stock_mk` where id >" + str(i) + " and id <=" + str(i + 100000)
            result = cursor.execute(sql)

            if result:
                data_list = cursor.fetchall()
                updateK2DB(data_list)
            i = i + 100000
            print('----Finish---' + str(i))

        except Exception as e:
            print(e)

    return True


#### 登陆系统 ####
lg = bs.login()
# 显示登陆返回信息
print('login respond error_code:' + lg.error_code)
print('login respond  error_msg:' + lg.error_msg)

### 获取证券信息 ####
rs = bs.query_all_stock(day="2019-04-30")
print('query_all_stock respond error_code:' + rs.error_code)
print('query_all_stock respond  error_msg:' + rs.error_msg)

queryKdata()

#### 登出系统 ####
bs.logout()

cursor.close()
db.close()

print('----OK---')
